package com.neusoft.chenanqi.dao;

import java.util.List;

import javax.inject.Inject;
import javax.validation.constraints.Null;

import org.springframework.stereotype.Repository;

import com.neusoft.chenanqi.dto.EmpSalDto;
import com.zero.basic.dao.DefaultBaseDao;
import com.zero.basic.dao.SqlListBaseDao;
import com.zero.basic.dao.SqlPagingListBaseDao;
import com.zero.basic.dao.SqlQueryBaseDao;
import com.zero.basic.dao.SqlUpdateBaseDao;
import com.zero.basic.exception.SmsDaoException;
import com.zero.basic.model.Pager;
import com.zero.mms.model.Dep;
import com.zero.mms.model.EmpSalary;
import com.zero.mms.model.Employee;
import com.zero.mms.model.SalaryItem;

@Repository("empSalDao")
public class EmpSalDaoImpl extends DefaultBaseDao<EmpSalary> implements
		EmpSalDao {

	@Inject
	private SqlPagingListBaseDao<?> sqlPagingListBaseDao;
	
	@Inject
	private SqlQueryBaseDao<?> sqlQueryBaseDao;
	
	@Inject
	private SqlListBaseDao<?> sqlListBaseDao;

	// 根据条件查询固定工资列表
	@Override
	public Pager<EmpSalDto> listEmpSal(String depName, String empNo, String startMonth, 
			String endMonth, Double salFrom, Double salTo, String type) throws SmsDaoException {
		try {
			String sql = "select * from (select distinct d.dep_name as depName, e.emp_id as empNo, "
					+ "e.emp_name as empName, s.emp_salary_date as month "
					+ "from t_emp_salary s, t_emp e, t_dep d, t_salary_item i "
					+ "where s.emp_id = e.emp_id and s.salary_item_id = i.salary_item_id "
					+ "and e.dep_id = d.dep_id and i.salary_item_type = '"
					+ type + "'";

			if (!"*".equals(depName)) {
				sql += " and d.dep_name like '%" + depName + "%'";
			}
			if (!"*".equals(empNo)) {
				sql += " and e.emp_id like '%" + empNo + "%'";
			}
			if (!"*".equals(startMonth)) {
				sql += " and s.emp_salary_date >= '" + startMonth + "'";
			}
			if (!"*".equals(endMonth)) {
				sql += " and s.emp_salary_date <= '" + endMonth + "'";
			}
			if (salFrom != null) {
				sql += " and s.emp_salary_num >= " + salFrom;
			}
			if (salTo != null) {
				sql += " and s.emp_salary_num <= " + salTo;
			}
			sql += " order by e.emp_id, s.emp_salary_date) as EmpFixedSal";
			Pager<EmpSalDto> empSalDtoList = sqlPagingListBaseDao.findBySql(sql, EmpSalDto.class, false);
			return empSalDtoList;
		} catch (Exception e) {
			throw new SmsDaoException();
		}
	}
	
	// 根据员工编号、月份和固定工资项目名查询工资项目
	@Override
	public EmpSalary getEmpSalByEmpNoMonthAndItemName(String empNo,
			String month, String itemName) throws SmsDaoException {
		try {
			String sql = "select * from t_emp_salary s, t_salary_item i "
					+ "where s.salary_item_id = i.salary_item_id and s.emp_id = '" + empNo
					+ "' and s.emp_salary_date = '" + month
					+ "' and i.salary_item_name = '" + itemName + "'";
			EmpSalary empSalary = (EmpSalary) sqlQueryBaseDao.queryBySql(sql, null, null, EmpSalary.class, true, true);
			return empSalary;
		} catch (Exception e) {
			throw new SmsDaoException();
		}
	}

	// 根据类型查询工资项目列表
	@Override
	public List<SalaryItem> listSalItemByType(String type)
			throws SmsDaoException {
		try {
			String sql = "select i.* from t_salary_item i "
					+ "where i.salary_item_type = '" + type + "'";
			List<SalaryItem> salaryItemList = sqlListBaseDao.listBySql(sql,
					SalaryItem.class, true, true);
			return salaryItemList;
		} catch (Exception e) {
			throw new SmsDaoException();
		}
	}

	// 根据名称和类型查询工资项目
	@Override
	public SalaryItem getSalItemByNameAndType(String name, String type) throws SmsDaoException {
		try {
			String sql = "select i.* from t_salary_item i "
					+ "where i.salary_item_name = '" + name + "' "
					+ "and i.salary_item_type = '" + type + "'";
			SalaryItem salaryItem = (SalaryItem) sqlQueryBaseDao.queryBySql(sql, null, SalaryItem.class, true, true);
			return salaryItem;
		} catch (Exception e) {
			throw new SmsDaoException();
		}
	}

	// 查询所有部门列表
	@Override
	public List<Dep> listDep() throws SmsDaoException {
		try {
			String sql = "select * from t_dep";
			List<Dep> depList = sqlListBaseDao.listBySql(sql, Dep.class, true, true);
			return depList;
		} catch (Exception e) {
			throw new SmsDaoException();
		}
	}

	// 根据部门ID查询员工列表
	@Override
	public List<Employee> listEmpByDepId(Integer depId) throws SmsDaoException {
		try {
			String sql = "select * from t_emp e where e.dep_id = ?";
			List<Employee> empList = sqlListBaseDao.listBySql(sql, depId, Employee.class, true, true);
			return empList;
		} catch (Exception e) {
			throw new SmsDaoException();
		}
	}
}
